package com.css.fxfzaqbz.modules.data.repository.impl;

import com.css.fxfzaqbz.constants.YNEnum;
import com.css.fxfzaqbz.modules.data.entity.GasstationData;
import com.css.fxfzaqbz.modules.data.entity.GeologicalDisasters;
import com.css.fxfzaqbz.modules.data.queryParams.GeologicalDisastersQueryParams;
import com.css.fxfzaqbz.modules.data.repository.GeologicalDisastersRepository;

import com.css.fxfzaqbz.util.PlatformObjectUtils;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


@Repository
public class GeologicalDisastersRepositoryImpl implements GeologicalDisastersRepository {

    @Resource
    private JdbcTemplate jdbcTemplate;
    @Resource
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;



    @Override
    public GeologicalDisasters saveGeologicalDisasters(GeologicalDisasters geologicalDisasters) {

        String sql = "insert into sys_geological_disasters values(:gdId,:gdNumber,:gdLongitude,:gdLatitude,:gdName,:gdType,:gdPeoNum,:gdHouseNum,:gdStability,:gdDanger,:gdStatus,:gdCreatetime,:gdUpdatetime)";

        SqlParameterSource source = new BeanPropertySqlParameterSource(geologicalDisasters);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        int i = namedParameterJdbcTemplate.update(sql, source, keyHolder);
        return geologicalDisasters;

    }

    @Override
    public GeologicalDisasters getGeologicalDisastersById(String id) {
        return jdbcTemplate.queryForObject("select * from sys_geological_disasters  where gd_id ='"+id+"' ",new BeanPropertyRowMapper<>(GeologicalDisasters.class));
    }

    @Override
    public void deleteGeologicalDisastersById(List<String> ids, Timestamp currentTimestamp) {
        String sql = "update sys_geological_disasters set gd_status = '" + YNEnum.N.toString() + "' ,gd_updatetime ='"+currentTimestamp+"' " +
                " where gd_id in (:ids)";
        Map map = new HashMap();
        map.put("ids", ids);
        namedParameterJdbcTemplate.update(sql, map);
    }

    @Override
    public List<GeologicalDisasters> getAll() {
        return jdbcTemplate.query("select * from sys_geological_disasters where gd_status = '"+YNEnum.Y+"'",new BeanPropertyRowMapper<>(GeologicalDisasters.class));
    }

    @Override
    public Page<GeologicalDisasters> getAllGeologicalDisastersParams(GeologicalDisastersQueryParams queryParams) {
        StringBuilder sql = new StringBuilder(" select  g.* from sys_geological_disasters g ");

          StringBuilder orderSql = new StringBuilder(" order by g.gd_createtime");

        StringBuilder whereSql = new StringBuilder(" where g.gd_status = '" + YNEnum.Y.toString() + "'");

//        if (!PlatformObjectUtils.isEmpty(queryParams.getDgdReslongitude())) {
//            whereSql.append(" and u.dgd_reslongitude like %" + queryParams.getDgdReslongitude() + "%");
//        }
//
//        if (!PlatformObjectUtils.isEmpty(queryParams.getDgdReslatitude())) {
//            whereSql.append(" and u.dgd_reslatitude like %" + queryParams.getDgdReslatitude() + "%");
//        }

        String wholeSql = sql.append(whereSql).append(orderSql).toString();
        List<GeologicalDisasters> list = jdbcTemplate.query(wholeSql, new BeanPropertyRowMapper<>(GeologicalDisasters.class));
        Pageable pageable = PageRequest.of(queryParams.getCurPage() - 1, queryParams.getPageSize());
        Page<GeologicalDisasters> geologicalEntities = new PageImpl<>(list, pageable, PlatformObjectUtils.isEmpty(list) ? 0 : list.size());
        return geologicalEntities;
    }



    @Override
    public void importExcel(List<GeologicalDisasters> geologicalDisasters) {
        jdbcTemplate.batchUpdate("insert into sys_geological_disasters (gd_id,gd_number,gd_longitude,gd_latitude,gd_name,gd_type,gd_peo_num,gd_house_num,gd_stability,gd_danger,gd_status,gd_createtime)values(?,?,?,?,?,?,?,?,?,?,?,?)", new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                System.out.println(geologicalDisasters.get(i).toString());
                ps.setString(1,geologicalDisasters.get(i).getGdId());
                ps.setString(2,geologicalDisasters.get(i).getGdNumber());
                ps.setFloat(3,geologicalDisasters.get(i).getGdLongitude());
                ps.setFloat(4,geologicalDisasters.get(i).getGdLatitude());
                ps.setString(5,geologicalDisasters.get(i).getGdName());
                ps.setString(6,geologicalDisasters.get(i).getGdType());
                ps.setObject(7,geologicalDisasters.get(i).getGdPeoNum());
                ps.setObject(8,geologicalDisasters.get(i).getGdHouseNum());
                ps.setString(9,geologicalDisasters.get(i).getGdStability());
                ps.setString(10,geologicalDisasters.get(i).getGdDanger());
                ps.setString(11,geologicalDisasters.get(i).getGdStatus());
                java.sql.Date createDate = new java.sql.Date(geologicalDisasters.get(i).getGdCreatetime().getTime());
                ps.setDate(12,  createDate);
            }
            @Override
            public int getBatchSize() {
                return geologicalDisasters.size();
            }
        });
    }

}
